# -*- coding: utf-8 -*-
import sys
import json
import yaml
import redis
import ConnES
import urllib
import urllib2
import pymongo
import ConnRedis
import ConnMySql
import ConnMongo
import SqlCollect
import ConfigParser
import traceback
import InitDB
import FindAndDelete
from JKlog import *

reload(sys)
sys.setdefaultencoding('utf-8')

class DbClear:
    def __init__(self, strConfig, UID, Token):
        self.UID = UID
        self.strConfig = strConfig
        self.action = SqlCollect.Action
        self.selete = self.action['select'].value
        self.delete = self.action['delete'].value
        self.token = Token

    # 定义主体mysql库查询删除逻辑
    def sqlexcute(self, logger, memdb, param, value):
        sql = self.selete + param + "'" + value + "'"
        FindAndDelete.findAndDelete().sqlFind(logger, memdb, sql)
        sql = self.delete + param + "'" + value + "'"
        FindAndDelete.findAndDelete().sqlDelete(logger, memdb, sql)
    def monogoexcute(self, logger, monogodb, param, value):
        FindAndDelete.findAndDelete().monogoFind(logger, monogodb, param, value)
        FindAndDelete.findAndDelete().monogoDelete(logger, monogodb, param, value)
    def esoexcute(self, logger, esdb, estype, param, value):
        FindAndDelete.findAndDelete().esfind(logger, esdb, estype, param, value)
        FindAndDelete.findAndDelete().esdelete(logger, esdb, estype, param, value)

    # 手机号移出白名单
    def removephone(self, logger, httpurl, PHONE):
        try:
            httpurl += PHONE
            logger.log(u'将手机号 ' + PHONE + ' 移出白名单，URL 为："%s" ' % httpurl)
            req = urllib2.Request(httpurl)
            response = urllib2.urlopen(req)
            logger.log(u'将手机号 ' + PHONE + ' 移出白名单，结果为："%s" ' % response.read())
        except:
            logger.log('移除白名单错误！')

    # 调用删除订单接口
    def deleteorder(self, logger, url, token, orderno, deletekey):
        flag = False
        try:
            data = {
                'orderNo': orderno,
                'deleteKey': deletekey
            }
            headers = {
                'Content-Type': 'application/json',
                'Authorization': 'Bearer %s'%token
            }
            request = urllib2.Request(url=url, headers=headers, data=json.dumps(data))
            response = urllib2.urlopen(request).read()
            if "200100" == response:
                logger.log(orderno + ' 订单删除成功。')
                flag = True
            else:
                logger.log(orderno + ' 订单删除失败，返回结果: ' + response)
        except Exception,e:
            logger.log('！！！请求删除订单接口异常！')
            logger.log('！！！请求订单删除接口URL：' + url)
            logger.log('！！！请求订单删除接口orderNO：' + orderno)
            logger.log('！！！请求订单删除接口异常信息：' + str(e)  )
        return flag

    def dataClear(self):
        # 定义全局变量
        flag = False
        logger = JKLogger('DbDataClean' + "_" + self.UID)
        mem_id = False
        acc_id = False
        try:
            # mem 库初始化
            memdb = InitDB.dbInit(self.strConfig).initMysql("mem")
            accDb = InitDB.dbInit(self.strConfig).initMysql("account")
            accPDb = InitDB.dbInit(self.strConfig).initMysql("pay")
            sharedb = InitDB.dbInit(self.strConfig).initMysql("share")
            orderdb = InitDB.dbInit(self.strConfig).initMysql("order")
            bdtdb = InitDB.dbInit(self.strConfig).initMysql("bdt")
            # ES 库初始化
            ESDb = InitDB.dbInit(self.strConfig).initES()
            # Mongo 初始化
            mongousers = InitDB.dbInit(self.strConfig).initMonogo("users")
            mongoquestion = InitDB.dbInit(self.strConfig).initMonogo("question")
            mongoquestion_msg = InitDB.dbInit(self.strConfig).initMonogo("question_msg")
            mongodoctorCommentPatient = InitDB.dbInit(self.strConfig).initMonogo("doctorCommentPatient")
            mongopatientCommentDoctor = InitDB.dbInit(self.strConfig).initMonogo("patientCommentDoctor")
            mongoinstantMessage = InitDB.dbInit(self.strConfig).initMonogo("instantMessage")
            mongooperatorLogger = InitDB.dbInit(self.strConfig).initMonogo("operatorLogger")
            # 移出白名单URL
            httpurl = InitDB.dbInit(self.strConfig).httpurl()
            deleteorderurl = InitDB.dbInit(self.strConfig).deleteorderurl()
            deletekey = InitDB.dbInit(self.strConfig).deletekey()

            # 删除订单接口（涉及网关、order服务）
            # 当 token 为空字符串时，不进行删除订单
            if self.token != "":
                sql = "SELECT * FROM" + SqlCollect.SqlCollect.sqlord01 + "'" + self.UID + "'"
                logger.log(u'查询SQL语句：' + sql )
                result = orderdb.fetch_all(sql)
                if result and len(result) > 0:
                    isflag = True
                    for res in result:
                        logger.log(u'查询结果：%s ' % repr(res).decode('unicode-escape'))
                        isflag = self.deleteorder(logger, deleteorderurl, self.token, res["ORDER_NO"], deletekey)
                        if not isflag:
                            return False
                else:
                    logger.log(u'查询结果：NULL ')

            # 获取mem表的 ID
            sql = SqlCollect.SqlCollect.sql_memID + "'" + self.UID + "'"
            result = memdb.fetch_all(sql)
            if result and len(result) == 1:
                mem_id = result[0]['ID']
                # 移出白名单
                self.removephone(logger, httpurl, result[0]['PHONE'])

            # 删除Monogo中数据
            self.monogoexcute( logger, mongousers, "_id", self.UID)
            self.monogoexcute( logger, mongoquestion, "uid", self.UID)
            sql = "SELECT DISTINCT ORDER_NO FROM" + SqlCollect.SqlCollect.sqlord01 + "'" + self.UID + "'"
            result = orderdb.fetch_all(sql)
            if result and len(result) > 0:
                for res in range(0,len(result)):
                    sql00 = "SELECT DISTINCT ROOMID FROM" + SqlCollect.SqlCollect.sqlroom01 + "'" + result[res]["ORDER_NO"] + "'"
                    result00 = orderdb.fetch_all(sql00)
                    if result00 and len(result00) > 0:
                        self.monogoexcute( logger, mongoquestion_msg, "to", result00[0]["ROOMID"])
                        self.monogoexcute( logger, mongoinstantMessage, "to", result00[0]["ROOMID"])
                    self.monogoexcute( logger, mongodoctorCommentPatient, "orderNo", result[res]["ORDER_NO"])
                    self.monogoexcute( logger, mongopatientCommentDoctor, "orderNo", result[res]["ORDER_NO"])
            self.monogoexcute(logger, mongodoctorCommentPatient, "patientUId", self.UID)
            self.monogoexcute(logger, mongopatientCommentDoctor, "patientUId", self.UID)
            self.monogoexcute(logger, mongodoctorCommentPatient, "doctorUId", self.UID)
            self.monogoexcute(logger, mongopatientCommentDoctor, "doctorUId", self.UID)
            self.monogoexcute( logger, mongooperatorLogger, "code", self.UID)

            # 删除ES中数据
            self.esoexcute( logger, ESDb, "es_doctor", "uid", self.UID)

            # member库相关sql
            # 用户相关数据 tbl_member
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlmer01,self.UID)
            # tbl_feedback
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlmer02,self.UID)
            # tbl_recommend
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlmer03, self.UID)
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlmer04, self.UID)

            # tbl_image 表
            if mem_id:
                self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlmer05, str(mem_id))
                # 医生相关数据
                sql = "SELECT HOSPITAL_CODE FROM tbl_doctor WHERE ID = " + "'" + str(mem_id) + "'"
                result = memdb.fetch_all(sql)
                if result and len(result) == 1:
                    hospital_code = result[0]["HOSPITAL_CODE"]
                    if hospital_code:
                        # 核对医院下是否包含其他医生
                        sql00 = "SELECT * FROM" + SqlCollect.SqlCollect.sqlhosp01 + "'" + hospital_code + "'"
                        result00 = memdb.fetch_all(sql00)
                        if result00 and len(result00) == 0:
                            sql01 = "SELECT * FROM" + SqlCollect.SqlCollect.sqlhosp02 + "'" + hospital_code + "'"
                            result01 = memdb.fetch_all(sql01)
                            if result01 and len(result01) == 0:
                                self.esoexcute(logger, ESDb, "es_hospital", "uid", hospital_code)
                                self.sqlexcute(logger, bdtdb, SqlCollect.SqlCollect.sqlhosp03, hospital_code)
                # 医生表 tbl_doctor
                self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqldoc01, str(mem_id))
                # tbl_doctor_servers
                self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqldoc02, str(mem_id))
                # tbl_doctor_verify
                self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqldoc03, str(mem_id))
                # 患者数据
                # tbl_record
                sql = SqlCollect.SqlCollect.sql_recordID + "'" + str(mem_id) + "'"
                result = memdb.fetch_all(sql)
                if result and len(result) > 0:
                    for res in range(0, len(result)):
                        self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat04, str(result[res]['RECORD_ID']))
                        # 获取 case_book_id
                        sql_casebookid = 'SELECT ID FROM' + SqlCollect.SqlCollect.sqlpat05 + "'" + str(result[res]['RECORD_ID']) + "'"
                        result_casebookid = memdb.fetch_all(sql_casebookid)
                        if result_casebookid and len(result_casebookid) > 0:
                            for n in range(0,len(result_casebookid)):
                                self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat09, str(result_casebookid[n]['ID']))
                        # tbl_case_book
                        self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat05, str(result[res]['RECORD_ID']))
                # 档案关系表 tbl_member_record
                self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat01, str(mem_id))

            #tbl_member_record_apply
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat02, self.UID)
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat03, self.UID)
            #tbl_case_book
            # 获取 case_book_id
            # 依据 uploader_id
            sql_casebookid = 'SELECT ID FROM' + SqlCollect.SqlCollect.sqlpat06 + "'" + self.UID + "'"
            result_casebookid = memdb.fetch_all(sql_casebookid)
            if result_casebookid and len(result_casebookid) > 0:
                for n in range(0, len(result_casebookid)):
                    self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat09, str(result_casebookid[n]['ID']))
            # 依据 doctor_code
            sql_casebookid = 'SELECT ID FROM' + SqlCollect.SqlCollect.sqlpat07 + "'" + self.UID + "'"
            result_casebookid = memdb.fetch_all(sql_casebookid)
            if result_casebookid and len(result_casebookid) > 0:
                for n in range(0, len(result_casebookid)):
                    self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat09, str(result_casebookid[n]['ID']))
            # 依据 CONSULT_DOCTOR_CODE
            sql_casebookid = 'SELECT ID FROM' + SqlCollect.SqlCollect.sqlpat08 + "'" + self.UID + "'"
            result_casebookid = memdb.fetch_all(sql_casebookid)
            if result_casebookid and len(result_casebookid) > 0:
                for n in range(0, len(result_casebookid)):
                    self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat09, res_casebookid[n]['ID'])
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat06, self.UID)
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat07, self.UID)
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat08, self.UID)
            # tbl_sns_friendship
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat10, self.UID)
            self.sqlexcute(logger,memdb,SqlCollect.SqlCollect.sqlpat11, self.UID)

            # account库相关
            # 以 uid 删除  获取变量值 , 获取 account_id
            sql = 'SELECT ID FROM' + SqlCollect.SqlCollect.sqlacc01 + "'" + self.UID + "'"
            result = accDb.fetch_all(sql)
            if result and len(result) == 1:
                acc_id = result[0]['ID']
            if acc_id:
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc01, self.UID)
                # 查找处银行卡并删除
                sql = 'SELECT BANK_CARD FROM' + SqlCollect.SqlCollect.sqlacc02 + "'" + str(acc_id) + "'"
                result = accDb.fetch_all(sql)
                if result and len(result) == 1:
                    self.sqlexcute(logger, accPDb, SqlCollect.SqlCollect.sqlban01, result[0]['BANK_CARD'])
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc02, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc03, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc04, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc05, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc06, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc07, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc08, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc09, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc10, str(acc_id))
                self.sqlexcute(logger, accDb, SqlCollect.SqlCollect.sqlacc11, str(acc_id))

            # share 库收益记录
            self.sqlexcute(logger, sharedb, SqlCollect.SqlCollect.sqlinc01, self.UID)
            # 将所有mysql库修改统一提交
            memdb.commit()
            accDb.commit()
            accPDb.commit()
            sharedb.commit()
            orderdb.commit()
            bdtdb.commit()
            # 关闭 monogo 数据库
            mongousers.close()
            mongoquestion.close()
            mongoquestion_msg.close()
            mongodoctorCommentPatient.close()
            mongoinstantMessage.close()
            mongooperatorLogger.close()
            mongopatientCommentDoctor.close()
            # 关闭 mysql 数据库
            memdb.close()
            accDb.close()
            accPDb.close()
            sharedb.close()
            orderdb.close()
            bdtdb.close()
            # 执行成功标志
            flag = True
        except:
            logger.log(traceback.format_exc())
            flag = False
        logger.delLogger()
        return flag